using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using NewsSpider.Model;
using NewsSpider.DBSqlHelp;
using System.Windows.Forms;

namespace NewsSpider.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2012，上海云里网络科技有限公司
    /// 内容摘要: ArticlesSQLiteDAL
    /// 完成日期：2012年1月31日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class ArticlesSQLiteDAL
    {
        NewsSpider.Utility.IniFile iniFile = new Utility.IniFile(Application.StartupPath + "\\data.ini");

        /// <summary>
        /// 获取Articles表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<Articles> GetAllArticlesDAL()
        {
            string strSql = "select * from " + iniFile.GetString("Table", "tablename", "");
            List<Articles> list = new List<Articles>();
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    Articles asnew = new Articles();
                    asnew.ArticleId = int.Parse(dr[iniFile.GetString("Table", "id", "")].ToString());
                    asnew.ArticleTitle = dr[iniFile.GetString("Table", "title", "")].ToString();
                    asnew.ArticleLink = dr[iniFile.GetString("Table", "link", "")].ToString();
                    asnew.ArticleContent = dr[iniFile.GetString("Table", "content", "")].ToString();
                    asnew.ArticleSource = dr[iniFile.GetString("Table", "source", "")].ToString();
                    asnew.ArticleAuthor = dr[iniFile.GetString("Table", "author", "")].ToString();
                    asnew.AddDate = Convert.ToDateTime(dr[iniFile.GetString("Table", "date", "")].ToString());

                    list.Add(asnew);
                }
                dr.Dispose();
                cn.Close();
                return list;
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }

            return list;
        }

        /// <summary>
        /// 根据指定SQL语句获取Articles表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<Articles> GetArticlesByWhereDAL(string strSql)
        {
            List<Articles> list = new List<Articles>();
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    Articles asnew = new Articles();
                    asnew.ArticleId = int.Parse(dr[iniFile.GetString("Table", "id", "")].ToString());
                    asnew.ArticleTitle = dr[iniFile.GetString("Table", "title", "")].ToString();
                    asnew.ArticleLink = dr[iniFile.GetString("Table", "link", "")].ToString();
                    asnew.ArticleContent = dr[iniFile.GetString("Table", "content", "")].ToString();
                    asnew.ArticleSource = dr[iniFile.GetString("Table", "source", "")].ToString();
                    asnew.ArticleAuthor = dr[iniFile.GetString("Table", "author", "")].ToString();
                    asnew.AddDate = Convert.ToDateTime(dr[iniFile.GetString("Table", "date", "")].ToString());

                    list.Add(asnew);
                }
                dr.Dispose();
                cn.Close();
                return list;
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }

            return list;
        }

        /// <summary>
        /// 往Articles表中插入数据
        /// <param name="asnew">实例化Articles类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertArticlesDAL(NewsSpider.Model.Articles asnew)
        {
            int iNum = 0;
            string strSql = "insert into " + iniFile.GetString("Table", "tablename", "") + "(" + iniFile.GetString("Table", "id", "") + "," + iniFile.GetString("Table", "title", "") + "," + iniFile.GetString("Table", "content", "") + "," + iniFile.GetString("Table", "author", "") + "," + iniFile.GetString("Table", "source", "") + "," + iniFile.GetString("Table", "link", "") + "," + iniFile.GetString("Table", "date", "") + ") " +
             " values(" + GetAutoAddNum() + ",'" + asnew.ArticleTitle + "','" + asnew.ArticleContent + "','" + asnew.ArticleAuthor + "','" + asnew.ArticleSource + "','" + asnew.ArticleLink + "',datetime('now','localtime'))";
            try
            {
                return NewsSpider.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除Articles表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteArticlesDAL(string strSql)
        {
            int iNum = 0;
            try
            {
                iNum = NewsSpider.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 更新Articles表中数据
        /// </summary>
        /// <param name="asnew">实例化Articles类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateArticlesDAL(NewsSpider.Model.Articles asnew)
        {
            int iNum = 0;
            string strSql = "update " + iniFile.GetString("Table", "tablename", "") + " set " + iniFile.GetString("Table", "title", "") + "='" + asnew.ArticleTitle + "'," + iniFile.GetString("Table", "content", "") + "='" + asnew.ArticleContent + "'," + iniFile.GetString("Table", "source", "") + "='" + asnew.ArticleSource + "'," + iniFile.GetString("Table", "author", "") + "='" + asnew.ArticleAuthor + "',article_link='" + asnew.ArticleLink + "' where " + iniFile.GetString("Table", "id", "") + "=" + asnew.ArticleId;
            try
            {
                iNum = NewsSpider.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 绑定表Articles到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindArticlesDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, iniFile.GetString("Table", "tablename", ""));
                ds.Tables[iniFile.GetString("Table", "tablename", "")].Columns.Add("ViewDate", typeof(string));
                ds.Tables[iniFile.GetString("Table", "tablename", "")].Columns.Add("ViewLink", typeof(string));
                for (int i = 0; i < ds.Tables["Articles"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables[iniFile.GetString("Table", "tablename", "")].Rows[i];
                    string strAuthor = dr[iniFile.GetString("Table", "author", "")].ToString();
                    if (string.IsNullOrEmpty(strAuthor))
                    {
                        dr[iniFile.GetString("Table", "author", "")] = "未采集";
                    }
                    string strSource = dr[iniFile.GetString("Table", "source", "")].ToString();
                    if (string.IsNullOrEmpty(strSource))
                    {
                        dr[iniFile.GetString("Table", "source", "")] = "未采集";
                    }
                    string strLink = dr[iniFile.GetString("Table", "link", "")].ToString();
                    dr["ViewLink"] = strLink;
                    dr[iniFile.GetString("Table", "link", "")] = "访问网页";
                    string strAddDate = Convert.ToDateTime(dr[iniFile.GetString("Table", "date", "")].ToString()).ToString("M月d日 HH:mm");
                    dr["ViewDate"] = strAddDate;
                }
                if (ds.Tables[iniFile.GetString("Table", "tablename", "")].Rows.Count > 0)
                {
                    dgv.DataSource = ds.Tables[iniFile.GetString("Table", "tablename", "")].DefaultView;
                }
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
        }

        /// <summary>
        /// 获取表中最大ID，并自增1
        /// </summary>
        /// <returns>返回表中最大ID，并自增1</returns>
        public int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select " + iniFile.GetString("Table", "id", "") + " from " + iniFile.GetString("Table", "tablename", "") + " order by " + iniFile.GetString("Table", "id", "") + " desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr[iniFile.GetString("Table", "id", "")].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 检测是否已存在数据库中，屏蔽已抓取的数据
        /// </summary>
        /// <param name="strCatchURL">原文链接</param>
        /// <returns>返回 True or False</returns>
        public bool CheckArticleIsExist(string strCatchURL)
        {
            bool bValue = false;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select " + iniFile.GetString("Table", "id", "") + " from " + iniFile.GetString("Table", "tablename", "") + " where " + iniFile.GetString("Table", "link", "") + "='" + strCatchURL + "'";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    bValue = true;
                }
                else
                {
                    bValue = false;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("检测是否已存在数据库中 -> " + ex.Message);
            }

            return bValue;
        }
    }
}
